﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Service.ZSJL.Com.Data.OracleDNet
{
    using System;
    using System.Data;
    using System.Configuration;

    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Text.RegularExpressions;
    using System.Text;
    using System.Data.OleDb;
    using System.Data.OracleClient;
    /// <summary>
    /// DataAccess 数据访问类
    /// <description>数据处理基类,调用方式：DataAccess.DataSet((string)sqlstr);或者DataAccess.DataSet((string)sqlstr,ref DataSet ds); </description>
    /// </summary>
    public class DataAccess
    {
        #region 属性
        protected OracleConnection conn = null;
        protected OracleCommand comm = null;
        protected OracleTransaction tran = null;
        OracleDataAdapter da = null;

        private string _connetStr = "";

        public string ConnetStr
        {
            get { return _connetStr; }
            set { _connetStr = value; }
        }
        #endregion
        public DataAccess()
        {
            //init();

        }

        public DataAccess(string connectString)
        {
            //init();

            this.ConnetStr = connectString;
            da = new OracleDataAdapter();
        }
        #region 内部函数 静态方法中不会执行DataAccess()构造函数



        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// 
        static public T QueryParameter<T>(string key, object empty)
        {
            T t = default(T);

            if (HttpContext.Current.Request.QueryString[key] != null)
            {
                try
                {

                    t = (T)Convert.ChangeType(HttpContext.Current.Request.QueryString[key], typeof(T));
                    return t;
                }
                catch
                {
                    t = (T)Convert.ChangeType(empty, typeof(T));

                    return t;
                }
            }

            t = (T)Convert.ChangeType(empty, typeof(T));

            return t;
        }
        public static string NoHTML(string Htmlstring)
        {
            //删除脚本  
            Htmlstring = Regex.Replace(Htmlstring, @"<script[^>]*?>.*?</script>", "", RegexOptions.IgnoreCase);
            //删除HTML  
            Htmlstring = Regex.Replace(Htmlstring, @"<(.[^>]*)>", "", RegexOptions.IgnoreCase);
            Htmlstring = Regex.Replace(Htmlstring, @"([\r\n])[\s]+", "", RegexOptions.IgnoreCase);
            Htmlstring = Regex.Replace(Htmlstring, @"-->", "", RegexOptions.IgnoreCase);
            Htmlstring = Regex.Replace(Htmlstring, @"<!--.*", "", RegexOptions.IgnoreCase);

            Htmlstring = Regex.Replace(Htmlstring, @"&(quot|#34);", "\"", RegexOptions.IgnoreCase);
            Htmlstring = Regex.Replace(Htmlstring, @"&(amp|#38);", "&", RegexOptions.IgnoreCase);
            Htmlstring = Regex.Replace(Htmlstring, @"&(lt|#60);", "<", RegexOptions.IgnoreCase);
            Htmlstring = Regex.Replace(Htmlstring, @"&(gt|#62);", ">", RegexOptions.IgnoreCase);
            Htmlstring = Regex.Replace(Htmlstring, @"&(nbsp|#160);", " ", RegexOptions.IgnoreCase);
            Htmlstring = Regex.Replace(Htmlstring, @"&(iexcl|#161);", "\xa1", RegexOptions.IgnoreCase);
            Htmlstring = Regex.Replace(Htmlstring, @"&(cent|#162);", "\xa2", RegexOptions.IgnoreCase);
            Htmlstring = Regex.Replace(Htmlstring, @"&(pound|#163);", "\xa3", RegexOptions.IgnoreCase);
            Htmlstring = Regex.Replace(Htmlstring, @"&(copy|#169);", "\xa9", RegexOptions.IgnoreCase);
            Htmlstring = Regex.Replace(Htmlstring, @"&#(\d+);", "", RegexOptions.IgnoreCase);

            Htmlstring.Replace("<", "");
            Htmlstring.Replace(">", "");
            Htmlstring.Replace("\r\n", "");
            Htmlstring = HttpContext.Current.Server.HtmlEncode(Htmlstring).Trim();

            return Htmlstring;
        }

        public OracleTransaction BeginTrans()
        {
            openConnection();
            tran = conn.BeginTransaction();
            comm.Transaction = tran;
            return tran;
        }

        public static void FileDown(string filename)
        {
            string FilePath = filename;
            System.IO.FileStream r = new System.IO.FileStream(FilePath, System.IO.FileMode.Open);

            HttpContext.Current.Response.Buffer = false;
            HttpContext.Current.Response.AddHeader("Connection", "Keep-Alive");
            HttpContext.Current.Response.ContentType = "application/octet-stream";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;

            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(System.IO.Path.GetFileName(FilePath)));
            HttpContext.Current.Response.AddHeader("Content-Length", r.Length.ToString());

            while (true)
            {
                byte[] buffer = new byte[1024];

                int leng = r.Read(buffer, 0, 1024);
                if (leng == 0)
                    break;
                if (leng == 1024)
                    HttpContext.Current.Response.BinaryWrite(buffer);
                else
                {

                    byte[] b = new byte[leng];
                    for (int i = 0; i < leng; i++)
                        b[i] = buffer[i];
                    HttpContext.Current.Response.BinaryWrite(b);
                }
            }
            r.Close();
            HttpContext.Current.Response.End();
        }
        public static int QueryInt(string s)
        {
            if (HttpContext.Current.Request.QueryString[s] != null)
            {

                string sp = HttpContext.Current.Request.QueryString[s].ToString();
                if (sp != "")
                {
                    return Convert.ToInt32(sp);
                }
                else
                {
                    return 1;
                }
            }
            else
            {
                return 1;
            }
        }
        private void openConnection()
        {
            if (string.IsNullOrEmpty(this.ConnetStr))
                throw new Exception("请先设置连接字符串！ConnectStr");
            if (conn == null)
                conn = new OracleConnection(this.ConnetStr);

            lock (conn)
            {
                if (conn.State == ConnectionState.Closed)
                {
                    //SysConfig.ConnectionString 为系统配置类中连接字符串

                    //   string strDbName = HttpContext.Current.Server.MapPath(@"~/App_Data/DY.mdb");
                    // conn.ConnectionString = ;
                    try
                    {
                        conn.Open();
                    }
                    catch (Exception e)
                    {
                        throw new Exception(e.Message);
                    }

                }
            }
        }
        private void virtualopenConnection()
        {
            if (conn.State == ConnectionState.Closed)
            {
                //SysConfig.ConnectionString 为系统配置类中连接字符串

                //string strDbName = HttpContext.Current.Server.MapPath(@"~/App_Data/DY.mdb");


                try
                {
                    //conn.Open();
                }
                catch (Exception e)
                {
                    throw new Exception(e.Message);
                }

            }
        }
        /// <summary>
        /// 关闭当前数据库连接
        /// </summary>
        public void closeConnection()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }

        }
        #endregion

        /// <summary>
        /// 执行Sql语句
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        public int ExecuteSql(string sqlstr)
        {
            return ExecuteSqlWithTransaction(sqlstr);
        }



        public string SafeRequest(string inputString, int maxLength)
        {
            StringBuilder retVal = new StringBuilder();

            if ((inputString != null) && (inputString != String.Empty))
            {
                inputString = inputString.Trim();

                if (inputString.Length > 0)
                {
                    inputString = inputString.Substring(0, maxLength);
                    for (int i = 0; i < inputString.Length; i++)
                    {
                        switch (inputString[i])
                        {
                            case '"':
                                retVal.Append("&quot;");
                                break;
                            case '<':
                                retVal.Append("&lt;");
                                break;
                            case '>':
                                retVal.Append("&gt;");
                                break;
                            default:
                                retVal.Append(inputString[i]);
                                break;
                        }
                    }
                }
                retVal.Replace("'", "");

            }
            return retVal.ToString();

        }
        public static string Query(string s)
        {
            try
            {
                if (HttpContext.Current.Request.QueryString[s] != null)
                {
                    return HttpContext.Current.Request.QueryString[s].ToString();
                }
                else
                {
                    return string.Empty;
                }
            }
            catch
            {
                return "";
            }
        }



        /// <summary>
        /// 执行Sql查询语句并返回指定列的第一条记录
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <returns>object 返回值 </returns>
        public object ExecuteScalar(string sqlstr)
        {
            object obj = new object();
            try
            {
                openConnection();
                CreateCommand(sqlstr);
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                obj = comm.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                closeConnection();
            }
            return obj;
        }

        /// <summary>
        /// 执行Sql查询语句,同时进行事务处理
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        public int ExecuteSqlWithTransaction(string sqlstr)
        {
            openConnection();
            tran = conn.BeginTransaction();
            try
            {
                CreateCommand(sqlstr);
                comm.Transaction = tran;
                int dd = comm.ExecuteNonQuery();
                tran.Commit();
                return dd;
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw ex;
            }
            finally
            {
                closeConnection();
            }
        }

        private void CreateCommand(string sqlstr)
        {
            comm = new OracleCommand(sqlstr, conn);
        }



        /// <summary>
        /// 返回指定Sql语句的OleDbDataReader，请注意，在使用后请关闭本对象，同时将自动调用closeConnection()来关闭数据库连接
        /// 方法关闭数据库连接
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <returns>OleDbDataReader对象</returns>
        public OracleDataReader DataReader(string sqlstr)
        {
            OracleDataReader dr = null;
            try
            {
                openConnection();
                comm.CommandText = sqlstr;
                comm.CommandType = CommandType.Text;
                dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                try
                {
                    dr.Close();
                    closeConnection();
                }
                catch
                {
                }
            }
            return dr;
        }

        /// <summary>
        /// 返回指定Sql语句的OleDbDataReader，请注意，在使用后请关闭本对象，同时将自动调用closeConnection()来关闭数据库连接
        /// 方法关闭数据库连接
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <param name="dr">传入的ref DataReader 对象</param>
        public void DataReader(string sqlstr, ref OracleDataReader dr)
        {
            try
            {
                openConnection();
                comm.CommandText = sqlstr;
                comm.CommandType = CommandType.Text;
                dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                try
                {
                    if (dr != null && !dr.IsClosed)
                        dr.Close();
                }
                catch
                {
                }
                finally
                {
                    closeConnection();
                }
            }
        }

        /// <summary>
        /// 返回指定Sql语句的DataSet
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <returns>DataSet</returns>
        public DataSet DataSet(string sqlstr)
        {
            DataSet ds = new DataSet();
            OracleDataAdapter da = new OracleDataAdapter();
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                da.SelectCommand = comm;
                da.SelectCommand.Connection = conn;
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                closeConnection();
            }
            return ds;
        }

        /// <summary>
        /// 返回指定Sql语句的DataSet
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <param name="ds">传入的引用DataSet对象</param>
        public void DataSet(string sqlstr, ref DataSet ds)
        {
            OracleDataAdapter da = new OracleDataAdapter();
            try
            {
                openConnection();
                comm.CommandType = CommandType.Text;
                comm.CommandText = sqlstr;
                da.SelectCommand = comm;
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                closeConnection();
            }
        }
        /// <summary>
        /// 返回指定Sql语句的DataTable
        /// </summary>
        /// <param name="sqlstr">传入的Sql语句</param>
        /// <returns>DataTable</returns>
        public DataTable DataTable(string sqlstr)
        {

            DataTable Datatable = new DataTable();
            // OracleTransaction trans = null;
            try
            {
                //closeConnection();
                openConnection();
                CreateCommand(sqlstr);
                // trans = conn.BeginTransaction();
                // comm.Transaction = trans;
                da.SelectCommand = comm;
                da.Fill(Datatable);
                //  trans.Commit();
            }
            catch (Exception ex)
            {
                //  trans.Rollback();
                throw ex;
            }
            finally
            {
                closeConnection();
            }
            return Datatable;
        }

        /// <summary>
        /// 批量更新或增加
        /// </summary>
        /// <param name="table"></param>
        /// <param name="connectionString"></param>
        /// <param name="insertCommand"></param>
        /// <returns></returns>
        public int BatchInsertUpdate(DataTable table,
OracleCommand[] insertCommand)
        {

            if (table == null)
                throw new Exception("table 数据为空，请填充数据再执行更新!");

            if (table.Rows.Count < 1)
                throw new Exception("table 数据为空，请填充数据再执行更新!");


            using (OracleConnection connection = new OracleConnection(this.ConnetStr))
            {

                connection.Open();
                OracleTransaction trans = connection.BeginTransaction();




                if (insertCommand.Count() > 0)
                {
                    insertCommand[0].Connection = connection;
                    insertCommand[0].Transaction = trans;
                }
                if (insertCommand.Count() > 1)
                {
                    insertCommand[1].Connection = connection;
                    insertCommand[1].Transaction = trans;
                }
                if (insertCommand.Count() > 2)
                {
                    insertCommand[2].Connection = connection;
                    insertCommand[2].Transaction = trans;
                }

                // When setting UpdateBatchSize to a value other than 1, all the commands 
                // associated with the SqlDataAdapter have to have their UpdatedRowSource 
                // property set to None or OutputParameters. An exception is thrown otherwise.







                int rowcount = 0;


                using (OracleDataAdapter adapter = new OracleDataAdapter())
                {
                    try
                    {
                        if (insertCommand.Count() > 0)
                        {
                            adapter.InsertCommand = insertCommand[0];
                            adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
                        }
                        if (insertCommand.Count() > 1)
                        {
                            adapter.UpdateCommand = insertCommand[1];
                            adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
                        }
                        if (insertCommand.Count() > 2)
                        {
                            adapter.DeleteCommand = insertCommand[2];
                            adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
                        }

                        // Gets or sets the number of rows that are processed in each round-trip to the server.
                        // Setting it to 1 disables batch updates, as rows are sent one at a time.
                        adapter.UpdateBatchSize = table.Rows.Count;

                        // insertCommand[0].UpdatedRowSource = UpdateRowSource.None;


                        // First process deletes.
                        rowcount += adapter.Update(table.Select(null, null, DataViewRowState.Deleted));

                        // Next process updates.
                        rowcount += adapter.Update(table.Select(null, null,
                           DataViewRowState.ModifiedCurrent));

                        // Finally, process inserts.
                        rowcount += adapter.Update(table.Select(null, null, DataViewRowState.Added));
                        // rowcount += adapter.Update(table.Select(null, null, DataViewRowState.Unchanged));
                        trans.Commit();
                        //    adapter.Update(table);

                        // Console.WriteLine("Successfully to update the table.");

                    }
                    catch (Exception ex)
                    {

                        trans.Rollback();
                        throw ex;

                    }
                }
                return rowcount;

            }
        }



        public bool UpdateTable(DataTable dt, string tableName)
        {
            try
            {
                openConnection();
                comm.CommandText = "select top 1 * from " + tableName;
                OracleDataAdapter ada = new OracleDataAdapter(comm);
                OracleCommandBuilder cmb = new OracleCommandBuilder(ada);
                ada.Update(dt);

                return true;
            }
            catch (Exception ex)
            {
                throw ex;

            }
            finally
            {
                closeConnection();
            }
        }


    }

}
